package br.com.fatecpg.minhasfinancas.sqlite;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import br.com.fatecpg.minhasfinancas.model.DespesaModel;

public class DespesaSqlite extends SQLiteOpenHelper {

	public DespesaSqlite(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	private static final int DATABASE_VERSION = 4;
	private static final String DATABASE_NAME = "financas";

	@Override
	public void onCreate(SQLiteDatabase db) {

		String query = "" + "CREATE TABLE tb_despesa ( "
				+ "cd_despesa INTEGER PRIMARY KEY NOT NULL, "
				+ "cd_forma_pagamento INTEGER NOT NULL,"
				+ "cd_cartao INTEGER NOT NULL,"
				+ "dc_despesa VARCHAR NULL,"
				+ "vl_despesa DECIMAL(8,2) NULL," 
				+ "dt_despesa DATE NULL,"
				+ "cd_tipo_despesa INTEGER NULL,"
				+ "cd_alerta_config INTEGER NULL" + ");";

		db.execSQL(query);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// db.execSQL("DROP TABLE IF EXISTS tb_usuario");
		this.onCreate(db);
	}

	private static final String TABLE_DESPESA = "tb_despesa";
	private static final String KEY_ID = "cd_despesa";
	private static final String KEY_FORMA_PAGAMENTO = "cd_forma_pagamento";
	private static final String KEY_ID_CARTAO = "cd_cartao";
	private static final String KEY_DESCRICAO = "dc_despesa";
	private static final String KEY_VALOR = "vl_despesa";
	private static final String KEY_DATA = "dt_despesa";
	private static final String KEY_TIPO_DESPESA = "cd_tipo_despesa";
	private static final String KEY_ALERTA = "cd_alerta_config";

	private static final String[] COLUMNS = { KEY_ID, KEY_FORMA_PAGAMENTO,
			KEY_ID_CARTAO, KEY_DESCRICAO, KEY_VALOR, KEY_DATA,
			KEY_TIPO_DESPESA, KEY_ALERTA };

	public void insert(DespesaModel model) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_FORMA_PAGAMENTO, model.getIdformaPagamento());
		values.put(KEY_ID_CARTAO, model.getIdcartao());
		values.put(KEY_DESCRICAO, model.getDescricao());
		values.put(KEY_VALOR, model.getValor());
		values.put(KEY_DATA, model.getData().toString());
		values.put(KEY_TIPO_DESPESA, model.getTipo());
		values.put(KEY_ALERTA, model.isAlerta());
		db.insert(TABLE_DESPESA, null, values);
		db.close();
	}

	public DespesaModel getDespesa(int id) {

		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_DESPESA, // a. table
				COLUMNS, " id = ?", new String[] { String.valueOf(id) }, null, // e.
																				// group
																				// by
				null, // f. having
				null, // g. order by
				null); // h. limit

		if (cursor != null)
			cursor.moveToFirst();

		SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
		Date convertedDate;
		try {
			convertedDate = dateFormat.parse(cursor.getString(5));
		} catch (Exception e) {
			convertedDate = new Date();
		}

		DespesaModel model = new DespesaModel();
		model.setId(Integer.parseInt(cursor.getString(0)));
		model.setIdformaPagamento(Integer.parseInt(cursor.getString(1)));
		model.setIdcartao(Integer.parseInt(cursor.getString(2)));
		model.setDescricao(cursor.getString(3));
		model.setValor(Integer.parseInt(cursor.getString(4)));
		model.setData(convertedDate);
		model.setTipo(cursor.getString(6));
		model.setAlerta(Boolean.parseBoolean(cursor.getString(7)));

				return model;
	}

	public List<DespesaModel> getListaDespesa() {
		List<DespesaModel> despesas = new LinkedList<DespesaModel>();

		String query = "SELECT  * FROM " + TABLE_DESPESA;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		DespesaModel despesa = null;
		if (cursor.moveToFirst()) {
			do {
				SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
				Date convertedDate;
				try {
					convertedDate = dateFormat.parse(cursor.getString(5));
				} catch (Exception e) {
					convertedDate = new Date();
				}

				despesa = new DespesaModel();
				despesa.setId(Integer.parseInt(cursor.getString(0)));
				despesa.setIdformaPagamento(Integer.parseInt(cursor
						.getString(1)));
				despesa.setIdcartao(Integer.parseInt(cursor.getString(2)));
				despesa.setDescricao(cursor.getString(3));
				despesa.setValor(Integer.parseInt(cursor.getString(4)));
				despesa.setData(convertedDate);
				despesa.setTipo(cursor.getString(6));
				despesa.setAlerta(Boolean.parseBoolean(cursor.getString(7)));

				despesas.add(despesa);
			} while (cursor.moveToNext());
		}
	
		return despesas;
	}
	
	public int update(DespesaModel model) {

		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_FORMA_PAGAMENTO, model.getIdformaPagamento());
		values.put(KEY_ID_CARTAO, model.getIdcartao());
		values.put(KEY_DESCRICAO, model.getDescricao());
		values.put(KEY_VALOR, model.getValor());
		values.put(KEY_DATA, model.getData().toString());
		values.put(KEY_TIPO_DESPESA, model.getTipo());
		values.put(KEY_ALERTA, model.isAlerta());

		int i = db.update(TABLE_DESPESA, values, KEY_ID + " = ?", // selections
				new String[] { String.valueOf(model.getId()) });

		db.close();

		return i;

	}

	public void delete(long id) {

		SQLiteDatabase db = this.getWritableDatabase();
		
		db.delete(TABLE_DESPESA, KEY_ID + " = ?",
				new String[] { String.valueOf(id) });

		db.close();

	}
}
